from flask import Flask, request, jsonify

from algorithm import median
from getData import before_get_data, update_mysql
from getWordCloud import GetWordCloud
from mysqlHelper import get_a_conn
from server import hdfsHelper
from sshHelper import exe_mr_app, exe_sqoop_app

# 创建应用程序
app = Flask(__name__)
app.config["SECRET_KEY"] = 'TPmi4aLWRbyVq8zu9v82dWYW1'

# pip install scrapy -i https://pypi.tuna.tsinghua.edu.cn/simple

# 租金预测
@app.route('/yuce', methods=['POST'])
def yuce():
    try:
        city_code = request.form.get('city_code', None)
        rent_mode = request.form.get('rent_mode', None)
        orientation = request.form.get('orientation', None)
        division = request.form.get('division', None)
        size = request.form.get('size', None)
        mysql = get_a_conn()
        sql = "SELECT rent FROM tbl_house WHERE 1=1 	"
        if city_code != None and city_code != '' and city_code != 'undefined':
            sql += " and city_code = '%s'" % (city_code)
        if rent_mode != None and rent_mode != '' and rent_mode != 'undefined':
            sql += " and rent_mode = '%s'" % (rent_mode)
        if orientation != None and orientation != '' and orientation != 'undefined':
            sql += " and orientation = '%s'" % (orientation)
        if division != None and division != '' and division != 'undefined':
            sql += " and division = '%s'" % (division)
        if size != None and size != '' and size != 'undefined':
            if size == '0':
                sql += "and (replace(size,'㎡','') < 50) "
            if size == '1':
                sql += "and (replace(size,'㎡','') < 90 and replace(size,'㎡','') > 50) "
            if size == '2':
                sql += "and (replace(size,'㎡','') < 120 and replace(size,'㎡','') > 90) "
            if size == '3':
                sql += "and (replace(size,'㎡','') > 120) "

        result = mysql.fetchall(sql)
        res = '0'
        if result:
            xinziList = ''
            for avg in result:
                xinziList += str(avg.get('rent'))
                xinziList += ','
            res = median(list(eval(xinziList)))
        return jsonify({'code': '200', 'info': res})
    except Exception as e:
        return jsonify({'code': '500', 'info': e})


# 生成词云
@app.route('/fuli', methods=['POST'])
def fuli():
    result = GetWordCloud()
    if result == '1':
        return jsonify({'code': '200', 'info': '生成词云图成功'})
    else:
        return jsonify({'code': '500', 'info': '生成词云图失败'})


# 租金分析
@app.route('/rent', methods=['POST'])
def zpXinzi():
    city_code = request.form.get('city_code', None)
    rent_mode = request.form.get('rent_mode', None)
    orientation = request.form.get('orientation', None)
    size = request.form.get('size', None)
    mysql = get_a_conn()
    sql = "SELECT CAST(sum( x.VALUE ) as CHAR)  value,x.name name from ( "
    sql += "SELECT count(rent) value, "
    sql += "case when rent < 1500 then '1500元内' "
    sql += "when rent >= 1500 && rent < 3000 then '1500元-3000元' "
    sql += "when rent >= 3000 && rent < 5000 then '3000元-5000元' "
    sql += "when rent >= 5000 && rent < 7000 then '5000元-7000元' "
    sql += "when rent >= 7000 then '大于7000元' "
    sql += "end name "
    sql += "FROM tbl_house  "
    sql += "WHERE 1=1 "
    if city_code != None and city_code != '':
        sql += " and city_code = '%s'" % (city_code)
    if rent_mode != None and rent_mode != '':
        sql += " and rent_mode = '%s'" % (rent_mode)
    if orientation != None and orientation != '':
        sql += " and orientation = '%s'" % (orientation)
    if size != None and size != '':
        if size == '0':
            sql += "and (replace(size,'㎡','') < 50) "
        if size == '1':
            sql += "and (replace(size,'㎡','') < 90 and replace(size,'㎡','') > 50) "
        if size == '2':
            sql += "and (replace(size,'㎡','') < 120 and replace(size,'㎡','') > 90) "
        if size == '3':
            sql += "and (replace(size,'㎡','') > 120) "
    sql += " GROUP BY rent) x GROUP BY x.name "
    result = mysql.fetchall(sql)
    return jsonify({'code': '200', 'info': result})


# 不同地区平均租房价格的分布情况
@app.route('/areaRentAvg', methods=['POST'])
def areaRentAvg():
    city_code = request.form.get('city_code', None)
    mysql = get_a_conn()
    sql = 'SELECT division name, CAST(convert(sum(rent)/count(1), decimal(12,2)) as CHAR) value FROM tbl_house WHERE 1=1  '
    if city_code != None and city_code != '':
        sql += " and city_code = '%s'" % (city_code)
    sql += ' group by division '
    result = mysql.fetchall(sql)
    return jsonify({'code': '200', 'info': result})


# 城市不同面积租房房价分布散点图
@app.route('/areaRent', methods=['POST'])
def areaRent():
    city_code = request.form.get('city_code', None)
    mysql = get_a_conn()
    sql = "SELECT REPLACE(size,'㎡','') size,rent FROM tbl_house WHERE 1=1 "
    if city_code != None and city_code != '':
        sql += " and city_code = '%s'" % (city_code)
    result = mysql.fetchall(sql)
    return jsonify({'code': '200', 'info': result})


# 城市不同区域租房数量分布情况
@app.route('/areaNum', methods=['POST'])
def areaNum():
    city_code = request.form.get('city_code', None)
    mysql = get_a_conn()
    sql = 'SELECT division name,count(division) value FROM tbl_house WHERE 1=1 '
    if city_code != None and city_code != '':
        sql += " and city_code = '%s'" % (city_code)
    sql += " GROUP BY division ORDER BY count(division) desc "
    result = mysql.fetchall(sql)
    return jsonify({'code': '200', 'info': result})


# 数据概览
@app.route('/getDatas', methods=['POST'])
def getDatas():
    pageno = int(request.json.get('pageNo', 1))
    pagesize = int(request.json.get('pageSize', 10))
    city = request.json.get('city', None)
    rent_mode = request.json.get('rent_mode', None)
    orientation = request.json.get('orientation', None)
    division = request.json.get('division', None)
    size = request.json.get('size', None)

    mysql = get_a_conn()
    sql = 'SELECT * FROM tbl_house  WHERE 1=1 '
    if city != None and city != '':
        sql += " and city_code = '%s'" % (city)
    if rent_mode != None and rent_mode != '' and rent_mode != 'undefined':
        sql += " and rent_mode = '%s'" % (rent_mode)
    if orientation != None and orientation != '' and orientation != 'undefined':
        sql += " and orientation = '%s'" % (orientation)
    if division != None and division != '' and division != 'undefined':
        sql += " and division = '%s'" % (division)
    if size != None and size != '' and size != 'undefined':
        if size == '0':
            sql += "and (replace(size,'㎡','') < 50) "
        if size == '1':
            sql += "and (replace(size,'㎡','') < 90 and replace(size,'㎡','') > 50) "
        if size == '2':
            sql += "and (replace(size,'㎡','') < 120 and replace(size,'㎡','') > 90) "
        if size == '3':
            sql += "and (replace(size,'㎡','') > 120) "
    sql += " ORDER BY create_time desc limit %s,%s" % ((pageno - 1) * pagesize, pagesize)
    result = mysql.fetchall(sql)
    sql_count = 'select count(1) num from tbl_house  WHERE 1=1 '
    if city != None and city != '':
        sql_count += " and city_code = '%s'" % (city)
    if rent_mode != None and rent_mode != '' and rent_mode != 'undefined':
        sql_count += " and rent_mode = '%s'" % (rent_mode)
    if orientation != None and orientation != '' and orientation != 'undefined':
        sql_count += " and orientation = '%s'" % (orientation)
    if division != None and division != '' and division != 'undefined':
        sql_count += " and division = '%s'" % (division)
    if size != None and size != '' and size != 'undefined':
        if size == '0':
            sql_count += "and (replace(size,'㎡','') < 50) "
        if size == '1':
            sql_count += "and (replace(size,'㎡','') < 90 and replace(size,'㎡','') > 50) "
        if size == '2':
            sql_count += "and (replace(size,'㎡','') < 120 and replace(size,'㎡','') > 90) "
        if size == '3':
            sql_count += "and (replace(size,'㎡','') > 120) "

    count = mysql.fetchall(sql_count)
    total = count[0].get('num')
    return jsonify({'code': '200', 'info': result, 'pageno': pageno, 'pagesize': pagesize, 'total': total})


# 获取日志
@app.route('/getLogs', methods=['POST'])
def getLogs():
    pageno = int(request.form.get('pageNo', 1))
    pagesize = int(request.form.get('pageSize', 10))
    mysql = get_a_conn()
    sql = "SELECT * FROM tbl_data_log ORDER BY end_time desc limit %s,%s" % ((pageno - 1) * pagesize, pagesize)
    result = mysql.fetchall(sql)
    sql_count = 'select count(1) num from tbl_data_log'
    count = mysql.fetchall(sql_count)
    total = count[0].get('num')
    return jsonify({'code': '200', 'info': result, 'pageno': pageno, 'pagesize': pagesize, 'total': total})


# 城市字典值获取
@app.route('/getDict', methods=['POST'])
def getDict():
    try:
        mysql = get_a_conn()
        sql = "SELECT * FROM tbl_city "
        result = mysql.fetchall(sql)
        return jsonify({'code': '200', 'info': result})
    except Exception as e:
        return jsonify({'code': '500', 'info': e})


# 区划查询
@app.route('/getDivisionList', methods=['POST'])
def getDivisionList():
    city_code = request.form.get('city_code', None)
    try:
        mysql = get_a_conn()
        sql = "SELECT division label ,division value FROM tbl_house WHERE 1=1 "
        if city_code != None and city_code != '':
            sql += " and city_code = '%s'" % (city_code)
        sql += " GROUP BY division"
        result = mysql.fetchall(sql)
        return jsonify({'code': '200', 'info': result})
    except Exception as e:
        return jsonify({'code': '500', 'info': e})


# 朝向列表查询
@app.route('/getOrientationList', methods=['POST'])
def getOrientationList():
    try:
        mysql = get_a_conn()
        sql = "SELECT orientation name,orientation value FROM tbl_house group by orientation; "
        result = mysql.fetchall(sql)
        return jsonify({'code': '200', 'info': result})
    except Exception as e:
        return jsonify({'code': '500', 'info': e})


# 开始运行
@app.route('/getJobData', methods=['POST'])
def getJobs():
    username = request.form.get('username', None)
    city = request.form.get('city', None)
    pagesize = request.form.get('pageSize', None)
    step = request.form.get('step', None)
    if step == '0':
        # 爬取数据，返回文件的地址
        result = before_get_data(username, city, pagesize)
        return jsonify({'code': '200', 'info': '爬取链家网成功，共爬取' + str(result) + '条数据\n'})

    if step == '1':
        # 拿到文件的地址，上传文件到 HDFS
        info = hdfsHelper.updateFile()
        return jsonify({'code': '200', 'info': str(info)+'\n'})

    if step == '2':
        # 运行 MapReduce 清洗数据写入 Hive
        info = exe_mr_app()
        return jsonify({'code': '200', 'info': "开始数据清洗\n"+str(info).replace("\\t","").replace("\\n","\n").replace(",","").replace("\[","").replace("]","").replace("\'","")+'\n'})

    if step == '3':
        # 把 Hive数据同步到 MySQL
        info = exe_sqoop_app()
        return jsonify({'code': '200', 'info': "开始数据同步\n"+str(info).replace("\\t","").replace("\\n","\n").replace(",","").replace("\[","").replace("]","").replace("\'","")+'\n'})

    if step == '4':
        # 更新到租房信息表
        update_mysql(city, username)
        return jsonify({'code': '200', 'info': '数据更新成功，请刷新\n'})

    return jsonify({'code': '500', 'info': '运行失败'})


# 修改密码
@app.route('/changePwd', methods=['POST'])
def changePwd():
    account = request.form.get('account', None)
    oldPwd = request.form.get('oldPwd', None)
    newPwd = request.form.get('newPwd', None)
    mysql = get_a_conn()
    sql = """ SELECT * FROM tbl_user t where t.account = '%s' and t.pwd = '%s' """ % (account, oldPwd)
    result = mysql.fetchall(sql)
    if (result):
        sql = "update tbl_user set pwd = '%s' WHERE account = '%s'" % (newPwd, account)
        result1 = mysql.fetchall(sql)
        print(result1)
        return jsonify({'code': '200', 'info': '修改成功'})
    else:
        return jsonify({'code': '500', 'info': '旧密码输入不正确'})


# 注册
@app.route('/reg', methods=['POST'])
def reg():
    try:
        account = request.form.get('account', None)
        pwd = request.form.get('pwd', None)
        name = request.form.get('name', None)

        # 账号是否存在校验
        mysql = get_a_conn()
        sql_username = 'select count(1) count from tbl_user t where t.account = "%s"' % (account)
        result = mysql.fetchall(sql_username)
        if (result[0].get('count') > 0):
            return jsonify({'code': '500', 'info': '该账号已注册'})
        else:
            sql = "INSERT into tbl_user (name,account,pwd,login_flag,role) values ('%s','%s','%s','%s','%s')" % (
                name, account, pwd, '1', '用户')
            mysql = get_a_conn()
            mysql.fetchall(sql)
        return jsonify({'code': '200', 'info': '注册成功，请登录'})
    except Exception as e:
        return jsonify({'code': '500', 'info': e})


# 登录
@app.route('/login', methods=['POST'])
def login():
    account = request.form.get('account', None)
    pwd = request.form.get('pwd', None)
    mysql = get_a_conn()
    sql = """ SELECT * FROM tbl_user t where t.account = '%s' and t.pwd = '%s' """ % (account, pwd)
    result = mysql.fetchall(sql)
    if (result):
        return {'code': '200', 'info': '%s登录成功' % account, 'session': result}
    else:
        return {'code': '500', 'info': '%s登录失败' % account}


if __name__ == '__main__':
    app.run(host="127.0.0.1", port=6666)  # 启动项目
